USE [master];


GO
CREATE DATABASE BlueM;


GO
USE BlueM;


GO
CREATE TABLE Employee
(
    EmployeeID        INT            IDENTITY PRIMARY KEY,
    EmployeeName      NVARCHAR (50)  NOT NULL,
    EmployeeSex       BIT            NOT NULL,
    EmployeeBirthday  DATE           NOT NULL,
    EmployeePosition  NVARCHAR (50)  NOT NULL,
    EmployeePhone     VARCHAR (50)   NOT NULL,
    EmployeeEmail     VARCHAR (50)   NULL,
    EmployeeAddress   NVARCHAR (100) NOT NULL,
    EmployeeCity      NVARCHAR (50)  NOT NULL,
    EmployeeCountry   NVARCHAR (50)  NOT NULL,
    EmployeeJobTitle  NVARCHAR (50)  NOT NULL,
    EmployeeImage     IMAGE          NOT NULL,
    EmployeeAccount   VARCHAR (50)   NULL,
    EmployeePassword  VARCHAR (50)   NULL,
    EmployeeAvailable BIT            NOT NULL
);


GO
CREATE TABLE Customer
(
    CustomerID         INT           IDENTITY PRIMARY KEY,
    CustomerName       NVARCHAR (50) NOT NULL,
    CustomerIdentifyNo VARCHAR (20)  NULL,
    CustomerPhone      VARCHAR (50)  NULL,
    CustomerEmail      VARCHAR (50)  NULL,
    CustomerAccount    VARCHAR (20)  NULL,
    CustomerSex        BIT           NOT NULL,
    ModDay             DATETIME      NOT NULL
);


GO
CREATE TABLE EquipmentDetail
(
    EquipmentDetailID INT             IDENTITY PRIMARY KEY,
    EquipmentName     NVARCHAR (50)   NOT NULL,
    EquipmentPrice    DECIMAL (11, 2) NOT NULL,
    Available         BIT             NOT NULL
);


GO
CREATE TABLE Promotion
(
    PromotionID          INT           IDENTITY PRIMARY KEY,
    PromotionStart       DATETIME      NOT NULL,
    PromotionEnd         DATETIME      NOT NULL,
    PromotionTitle       NVARCHAR (50) NOT NULL,
    PromotionDescription VARCHAR (500) NULL,
    PromotionBanner      IMAGE         NULL
);

CREATE TABLE SupplyDetail
(
    SupplyDetailID INT             IDENTITY PRIMARY KEY,
    SupplyName     NVARCHAR (50)   NOT NULL,
    SupplyPrice    DECIMAL (11, 2) NOT NULL,
    Unit           NVARCHAR (50)   NOT NULL,
    Available      BIT             NOT NULL
);


GO
CREATE TABLE RoomType
(
    RoomTypeID    INT             IDENTITY PRIMARY KEY,
    RoomTypeTitle VARCHAR (20)    NOT NULL,
    RoomRank      VARCHAR (50)    NOT NULL,
    RoomPrice     DECIMAL (11, 2) NOT NULL,
    Bath          BIT             NOT NULL,
    [View]        BIT             NOT NULL,
    Balcony       BIT             NOT NULL
);

CREATE TABLE Room
(
    RoomID     INT PRIMARY KEY,
    RoomTypeID INT NOT NULL,
    [Status]     INT NOT NULL,
    CONSTRAINT FK_Room_RoomType FOREIGN KEY (RoomTypeID) REFERENCES RoomType (RoomTypeID) ON DELETE NO ACTION ON UPDATE NO ACTION
);


GO
CREATE TABLE PromotionDetail
(
    PromotionDetailID INT             IDENTITY PRIMARY KEY,
    PromotionID       INT             NOT NULL,
    PromotionContent  NVARCHAR (1000) NOT NULL,
    PromotionImage    IMAGE           NULL,
    CONSTRAINT FK_PromitionDetail_Promotion FOREIGN KEY (PromotionID) REFERENCES Promotion (PromotionID) ON DELETE NO ACTION ON UPDATE NO ACTION
);


GO
CREATE TABLE EmployeeLog
(
    LogID       INT          IDENTITY PRIMARY KEY,
    LoginTime   DATETIME     NOT NULL,
    ServiceName VARCHAR (40) NOT NULL,
    EmployeeID  INT          NOT NULL,
    CONSTRAINT FK_EmployeeLog_Employee FOREIGN KEY (EmployeeID) REFERENCES Employee (EmployeeID) ON DELETE NO ACTION ON UPDATE NO ACTION
);


GO
CREATE TABLE Equipment
(
    EquipmentID       INT IDENTITY PRIMARY KEY,
    EquipmentDetailID INT NOT NULL,
    RoomTypeID        INT NOT NULL,
    CONSTRAINT FK_Equipment_EquipmentDetail FOREIGN KEY (EquipmentDetailID) REFERENCES EquipmentDetail (EquipmentDetailID) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT FK_Equipment_RoomType FOREIGN KEY (RoomTypeID) REFERENCES RoomType (RoomTypeID) ON DELETE NO ACTION ON UPDATE NO ACTION
);


GO
CREATE TABLE Booking
(
    BookingID    INT             IDENTITY PRIMARY KEY,
    BookingDate  DATETIME        NOT NULL,
    CustomerID   INT             NOT NULL,
    TotalPayment DECIMAL (11, 2) NULL,
    CONSTRAINT FK_Booking_Customer FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID) ON DELETE NO ACTION ON UPDATE NO ACTION
);


GO
CREATE TABLE CheckIn
(
    CheckInID        INT             IDENTITY PRIMARY KEY,
    BookingID        INT             NULL,
    RoomID           INT             NOT NULL,
    FeedBack         NVARCHAR (500)  NULL,
    CheckInDate      DATETIME        NOT NULL,
    CheckInEmployee  INT             NULL,
    CheckOutDate     DATETIME        NOT NULL,
    CheckOutEmployee INT             NULL,
    GuestInfo        VARCHAR (400)   NULL,
    Payment          DECIMAL (11, 2) NULL,
    Available        BIT             NOT NULL,
    CONSTRAINT FK_CheckIn_Booking FOREIGN KEY (BookingID) REFERENCES Booking (BookingID) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT FK_CheckIn_Room FOREIGN KEY (RoomID) REFERENCES Room (RoomID) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT FK_CheckIn_Employee1 FOREIGN KEY (CheckInEmployee) REFERENCES Employee (EmployeeID) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT FK_CheckIn_Employee2 FOREIGN KEY (CheckOutEmployee) REFERENCES Employee (EmployeeID) ON DELETE NO ACTION ON UPDATE NO ACTION
);


GO
CREATE TABLE Supply
(
    SupplyID       INT      IDENTITY PRIMARY KEY,
    SupplyDate     DATETIME NOT NULL,
    CheckInID      INT      NOT NULL,
    SupplyDetailID INT      NOT NULL,
    SupplyNumber   INT      NOT NULL,
    CONSTRAINT FK_Supply_CheckIn FOREIGN KEY (CheckInID) REFERENCES CheckIn (CheckInID) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT FK_Supply_SupplyDetail FOREIGN KEY (SupplyDetailID) REFERENCES SupplyDetail (SupplyDetailID) ON DELETE NO ACTION ON UPDATE NO ACTION
);


GO
CREATE VIEW vSearchRoom
AS
    SELECT dbo.Room.RoomID,
           dbo.Room.[Status],
           dbo.RoomType.RoomTypeID,
           dbo.RoomType.RoomTypeTitle,
           dbo.RoomType.RoomPrice,
           dbo.CheckIn.CheckInDate,
           dbo.CheckIn.CheckOutDate,
           dbo.CheckIn.Available,
           dbo.RoomType.Balcony,
           dbo.RoomType.Bath,
           dbo.RoomType.[View]
    FROM   dbo.CheckIn
           INNER JOIN
           dbo.Room
           ON dbo.CheckIn.RoomID = dbo.Room.RoomID
           INNER JOIN
           dbo.RoomType
           ON dbo.Room.RoomTypeID = dbo.RoomType.RoomTypeID;


GO
CREATE VIEW vEmployeeSubDetail
AS
    SELECT dbo.Employee.EmployeeID,
           dbo.Employee.EmployeeName,
           dbo.Employee.EmployeeSex,
           dbo.Employee.EmployeePosition,
           dbo.Employee.EmployeePhone,
           dbo.Employee.EmployeeEmail,
           dbo.Employee.EmployeeJobTitle,
           dbo.Employee.EmployeeAvailable
    FROM   dbo.Employee;


GO
CREATE VIEW vRoomInfo
AS
    SELECT dbo.Room.RoomID,
           dbo.RoomType.RoomTypeID,
           dbo.RoomType.RoomTypeTitle,
           dbo.RoomType.RoomRank,
           dbo.RoomType.Bath,
           dbo.RoomType.[View],
           dbo.RoomType.Balcony,
           dbo.RoomType.RoomPrice,
           dbo.Room.[Status]
    FROM   dbo.Room
           INNER JOIN
           dbo.RoomType
           ON dbo.Room.RoomTypeID = dbo.RoomType.RoomTypeID;


GO
CREATE VIEW [dbo].[vSubPromotion]
AS
    SELECT PromotionID,
           PromotionStart,
           PromotionEnd,
           PromotionTitle,
           PromotionDescription
    FROM   dbo.Promotion;


GO
CREATE VIEW [dbo].[vSubPromotionDetail]
AS
    SELECT PromotionDetailID,
           PromotionID,
           PromotionContent
    FROM   dbo.PromotionDetail;


GO
CREATE VIEW [dbo].[vBill]
AS
    SELECT dbo.Customer.CustomerID,
           dbo.Customer.CustomerName,
           dbo.Customer.CustomerIdentifyNo,
           dbo.Customer.CustomerAccount,
           dbo.Booking.BookingDate,
           dbo.Booking.BookingID,
           dbo.Booking.TotalPayment,
           dbo.CheckIn.CheckInID,
           dbo.CheckIn.Payment,
           dbo.CheckIn.CheckInDate,
           dbo.CheckIn.CheckOutDate,
           dbo.Supply.SupplyID,
           dbo.Supply.SupplyDate,
           dbo.Supply.SupplyNumber,
           dbo.SupplyDetail.SupplyDetailID,
           dbo.SupplyDetail.SupplyName,
           dbo.SupplyDetail.SupplyPrice,
           dbo.SupplyDetail.Unit,
           dbo.RoomType.RoomPrice,
           dbo.RoomType.RoomTypeTitle,
           dbo.Room.RoomID
    FROM   dbo.Booking
           INNER JOIN
           dbo.CheckIn
           ON dbo.Booking.BookingID = dbo.CheckIn.BookingID
           INNER JOIN
           dbo.Customer
           ON dbo.Booking.CustomerID = dbo.Customer.CustomerID
           INNER JOIN
           dbo.Room
           ON dbo.CheckIn.RoomID = dbo.Room.RoomID
           INNER JOIN
           dbo.RoomType
           ON dbo.Room.RoomTypeID = dbo.RoomType.RoomTypeID
           LEFT OUTER JOIN
           dbo.Supply
           ON dbo.CheckIn.CheckInID = dbo.Supply.CheckInID
           LEFT OUTER JOIN
           dbo.SupplyDetail
           ON dbo.Supply.SupplyDetailID = dbo.SupplyDetail.SupplyDetailID;


GO
CREATE VIEW [dbo].[vSupplyOrder]
AS
    SELECT dbo.Supply.SupplyID,
           dbo.Supply.SupplyNumber,
           dbo.Supply.SupplyDetailID,
           dbo.SupplyDetail.SupplyName,
           dbo.SupplyDetail.SupplyPrice,
           dbo.Supply.CheckInID
    FROM   dbo.Supply
           INNER JOIN
           dbo.SupplyDetail
           ON dbo.Supply.SupplyDetailID = dbo.SupplyDetail.SupplyDetailID;


GO
CREATE VIEW [dbo].[vReportCustomerCheck]
AS
    SELECT   dbo.Customer.CustomerID,
             dbo.Customer.CustomerName,
             COUNT(DISTINCT dbo.Booking.BookingID) AS BookingCount,
             COUNT(dbo.CheckIn.CheckInID) AS CheckInCount,
             COUNT(dbo.CheckIn.Payment) AS CheckOutCount
    FROM     dbo.Customer
             INNER JOIN
             dbo.Booking
             ON dbo.Customer.CustomerID = dbo.Booking.CustomerID
             LEFT OUTER JOIN
             dbo.CheckIn
             ON dbo.Booking.BookingID = dbo.CheckIn.BookingID
    GROUP BY dbo.Customer.CustomerID, dbo.Customer.CustomerName;


GO
CREATE VIEW [dbo].[vIncome]
AS
    SELECT BookingDate,
           BookingID,
           TotalPayment
    FROM   dbo.Booking;


GO
CREATE VIEW [dbo].[vTotalCheck]
AS
    SELECT   CheckInDate,
             CheckOutDate,
             COUNT(CheckOutEmployee) AS CHECKOUT,
             COUNT(CheckInEmployee) AS CHECKIN,
             SUM(Payment) AS TOTALPAYMENT
    FROM     dbo.CheckIn
    WHERE    (CheckInEmployee > 0)
    GROUP BY CheckInDate, CheckOutDate;

